package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {

	public static void createTables() throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("drop table if exists database");
			statement
					.executeUpdate("create table database (id integer, record string)");

			statement.executeUpdate("drop table if exists queue");
			statement
					.executeUpdate("create table queue (id integer, command string, "
							+ "sok integer, record string)");

			statement.executeUpdate("drop table if exists reserved");
			statement.executeUpdate("create table reserved (id integer)");

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}

	}

	public static void addToQueue(int sok, String command, String record,
			int recordId) throws ClassNotFoundException {
		add("queue", recordId + ", '" + command + "', " + sok + ",'" + record
				+ "'");
	}

	public static void add(int id, String record) throws ClassNotFoundException {
		add("database", id + ", '" + record + "'");
	}

	public static void reserve(int id) throws ClassNotFoundException {
		add("reserved", "" + id);
	}

	public static void add(String tablename, String values)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("insert into " + tablename + " values("
					+ values + ")");
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	public static void printQueue() throws ClassNotFoundException {
		// load the sqlite-JDBC driver using the current class loader
		Class.forName("org.sqlite.JDBC");

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement.executeQuery("select * from queue");
			while (rs.next()) {
				// read the result set
				System.out.println("record = " + rs.getString("record"));
				System.out.println("sok = " + rs.getInt("sok"));
				System.out.println("command = " + rs.getString("command"));
				System.out.println("recordId = " + rs.getInt("id"));
			}
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	public static String printDB() throws ClassNotFoundException {
		// load the sqlite-JDBC driver using the current class loader
		Class.forName("org.sqlite.JDBC");
		String db = "";
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement.executeQuery("select * from database");
			while (rs.next()) {
				// read the result set
				db += "{id = " + rs.getString("record") + ", record="
						+ rs.getString("record") + "}";
			}
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
		return db;
	}

	public static boolean isReserved(Integer id) throws ClassNotFoundException {
		return tableContainsElement("reserved", id);
	}

	public static void unreserve(Integer id) throws ClassNotFoundException {
		remove("reserved", id);
	}

	public static boolean isQueued(Integer id) throws ClassNotFoundException {
		return tableContainsElement("queue", id);
	}

	private static boolean tableContainsElement(String tablename, Integer id)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement.executeQuery("select * from " + tablename
					+ " where id = " + id);
			if (rs.next()) {
				rs.getInt("id");
				return true;
			}
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
		return false;
	}

	// private static void performeUpdate(String query) throws
	// ClassNotFoundException {
	// Class.forName("org.sqlite.JDBC");
	// Connection connection = null;
	// try {
	// // create a database connection
	// connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
	// Statement statement = connection.createStatement();
	// statement.setQueryTimeout(30); // set timeout to 30 sec.
	//
	// int newSok;
	// statement.executeUpdate(query);
	// } catch (SQLException e) {
	// // if the error message is "out of memory",
	// // it probably means no database file is found
	// System.err.println(e.getMessage());
	// } finally {
	// try {
	// if (connection != null)
	// connection.close();
	// } catch (SQLException e) {
	// // connection close failed.
	// System.err.println(e);
	// }
	// }
	// }

	public static void updateOKs(Integer id, Integer i)
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement
					.executeQuery("select * from queue where id = " + id);
			if (rs.next()) {
				int newSok = rs.getInt("sok") + i;
				statement.executeUpdate("update queue set sok=" + newSok);
			}

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	public static Queue getAction(Integer id) throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement
					.executeQuery("select * from queue where id = " + id);
			if (rs.next()) {
				return new Queue( rs.getInt("sok"),  rs.getString("command"),  rs.getInt("id"),  rs.getString("record"));
			}

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	return null;
	}

	public static void removeFromQueue(Integer id) throws ClassNotFoundException {
		remove("queue",id);
	}
	
	
	private static void remove(String tablename, int id)throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("delete from "+tablename+" where id = " + id);

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	}

	public static int getSok(Integer id) 
			throws ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement
					.executeQuery("select * from queue where id = " + id);
			if (rs.next()) {
				return rs.getInt("sok");
			}

		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
		return 0;
	}

	public static void printReserved() throws ClassNotFoundException {
			// load the sqlite-JDBC driver using the current class loader
			Class.forName("org.sqlite.JDBC");

			Connection connection = null;
			try {
				// create a database connection
				connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
				Statement statement = connection.createStatement();
				statement.setQueryTimeout(30); // set timeout to 30 sec.

				ResultSet rs = statement.executeQuery("select * from reserved");
				while (rs.next()) {
					// read the result set
					System.out.println("id = " + rs.getString("id"));
				}
			} catch (SQLException e) {
				// if the error message is "out of memory",
				// it probably means no database file is found
				System.err.println(e.getMessage());
			} finally {
				try {
					if (connection != null)
						connection.close();
				} catch (SQLException e) {
					// connection close failed.
					System.err.println(e);
				}
			}
		}

}
